package scsc.pm;

import java.util.*;

import scsc.pub.data.NohouseBuilding;
import scsc.pub.db.*;
import scsc.pub.tools.*;
import java.util.ArrayList;
import scsc.web.xzgl.building.report.data.BusinessReportData;
import scsc.pub.exception.*;

public class BusinessReportPm {
  private static Logger logger = Logger.getLogger(BusinessReportPm.class);
  public BusinessReportPm() {
  }

  public void selectBusinessReport(DBManage dbmanage, BusinessReportData formdata,
                                  PageListData footer) {
	 String selBeginDate=formdata.getSelStartDate();
	 String selEndDate=formdata.getSelEndDate();
	 String selUnit=formdata.getTenancyUnit();
	String sqlStr="select new scsc.web.xzgl.building.report.data.BusinessReportData(";
	sqlStr+="h.roomId,h.room,h.roomPlace,h.tenancyUnit,h.corporation,sum(r.rentFee+r.waterFee+r.warmFee+r.elecFee) as total,h.tel";
	sqlStr+=")";
	sqlStr+=" from scsc.pub.data.BusinessHouse as h,scsc.pub.data.BusinessHouseRentFee as r where r.roomId=h.roomId";
	if (selUnit!=null && selUnit.length()>0)
		sqlStr+=" and h.tenancyUnit like '%" + selUnit + "%'";
	if (selBeginDate!=null && selBeginDate.length()>0)
		sqlStr+=" and r.beginDate>='" + selBeginDate + "'";
	if (selEndDate!=null && selEndDate.length()>0)
		sqlStr+=" and r.endDate<='" + selEndDate + "'";
	sqlStr+=" group by h.roomId,h.room,h.roomPlace,h.tenancyUnit,h.corporation,h.tel";
	dbmanage.sqlQueryPage(sqlStr, footer);

  }

  public void selectBusinessDetailReport(DBManage dbmanage, BusinessReportData formdata,
          PageListData footer){
	  String selBeginDate=formdata.getSelStartDate();
	  String selEndDate=formdata.getSelEndDate();
	  String selUnit=formdata.getTenancyUnit();
	  String sqlStr="select new scsc.web.xzgl.building.report.data.BusinessReportData(";
	  sqlStr+="h.roomId,h.room,h.roomPlace,h.tenancyUnit,h.corporation,r.beginDate,r.endDate,r.rentFeeStd,r.rentFee,r.waterFee,r.elecFee,r.warmFee,h.tel";
	  sqlStr+=")";
	  sqlStr+=" from scsc.pub.data.BusinessHouse as h,scsc.pub.data.BusinessHouseRentFee as r where r.roomId=h.roomId";
		if (selUnit!=null && selUnit.length()>0)
			sqlStr+=" and h.tenancyUnit like '%" + selUnit + "%'";
//		if (selBeginDate!=null && selBeginDate.length()>0)
//			sqlStr+=" and r.beginDate>='" + selBeginDate + "'";
//		if (selEndDate!=null && selEndDate.length()>0)
//			sqlStr+=" and r.endDate<='" + selEndDate + "'";
		if (selBeginDate!=null && selBeginDate.length()>0 && selEndDate!=null && selEndDate.length()>0){
			sqlStr=sqlStr + " and (";
			sqlStr=sqlStr + " r.beginDate<='" + selBeginDate + "' and r.endDate>='" + selBeginDate + "'";
			sqlStr=sqlStr + " or r.beginDate<='" + selEndDate + "' and r.endDate>='" + selEndDate + "'";
			sqlStr=sqlStr + " or r.beginDate>='" + selBeginDate + "' and r.endDate<='" + selEndDate + "'";
			sqlStr=sqlStr + ")";
		}
		sqlStr+=" order by h.roomId";
		dbmanage.sqlQueryPage(sqlStr, footer);
	  
  }
   
  public ArrayList selectPeriod(DBManage dbmanage,String sdate,String edate,Integer roomId,String selUnit){
	  String sqlStr="select new scsc.web.xzgl.building.report.data.BusinessReportData(";
	  sqlStr+="h.roomId,h.room,h.roomPlace,h.tenancyUnit,h.corporation,r.beginDate,r.endDate,r.rentFeeStd,r.rentFee,r.waterFee,r.elecFee,r.warmFee,h.tel";
	  sqlStr=sqlStr + ")";
	  sqlStr=sqlStr + " from scsc.pub.data.BusinessHouse as h,scsc.pub.data.BusinessHouseRentFee as r where r.roomId=h.roomId";
	  sqlStr=sqlStr + " and (";
	  sqlStr=sqlStr + " r.beginDate<='" + sdate + "' and r.endDate>='" + sdate + "'";
	  sqlStr=sqlStr + " or r.beginDate<='" + edate + "' and r.endDate>='" + edate + "'";
	  sqlStr=sqlStr + " or r.beginDate>='" + sdate + "' and r.endDate<='" + edate + "'";
	  sqlStr=sqlStr + ")";
	  if (roomId!=null && roomId.intValue()>0){
		  sqlStr=sqlStr + " and r.roomId=" + roomId;
	  }
	  if (selUnit!=null && selUnit.length()>0)
			sqlStr+=" and h.tenancyUnit like '%" + selUnit + "%'";
	  List result = dbmanage.sqlQuery(sqlStr);
	  return (ArrayList) result;

	  
  }
  


  
}
